USE [CXML_Object_Store]
GO
/****** Object:  StoredProcedure [dbo].[GetBusinessEntitiesInSolution]    Script Date: 03/29/2010 16:42:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetBusinessEntitiesInSolution]
   @Solution_ID int
as
  set nocount on
select 
s.vch_SolutionName as 'Solution_Name',
p.vch_ProjectName as 'Project_Name',
sc.idgk_ScopeID as 'ScopeID',
sc.vch_ScopeName as 'Scope_Name',
c.idgk_ContextID as 'ContextID',
c.vch_Name as 'Context_Name',
et.idgk_EntityTypeID as 'EntityTypeID',
et.vch_Name as 'EntityType',
ev.vch_MajorVersion + '.' + ev.vch_MinorVersion + '.' + isnull(RVV.vch_MajorVersion,'000') as 'VersionNumber',
ev.idgk_EntityVersionID as 'VersionID'
from Solution s 
join solution_x_project sp on s.idgk_SolutionID = sp.fk_SolutionID
join project p on sp.fk_ProjectID = p.idgk_ProjectID and sp.fk_ProjectID = p.idgk_ProjectID
join project_X_Context pc on pc.fk_ProjectID = p.idgk_ProjectID
join context c on pc.fk_ContextID = c.idgk_ContextID
join project_x_scope ps on ps.fk_ProjectID = p.idgk_ProjectID
join scope sc on sc.idgk_ScopeID = ps.fk_ScopeID
join project_x_entitytypeversions petv on sp.fk_ProjectID = petv.fk_ProjectID
join entitytypeversions etv on petv.fk_EntityTypeVersionsID = etv.idgk_EntityTypeVersionsID
join entitytype_x_context etc on c.idgk_ContextID = etc.fk_ContextID and etc.fk_EntityTypeID = etv.fk_EntityTypeID
join entitytype et on etv.fk_EntityID = et.idgk_EntityTypeID
join entityversion ev on etv.fk_EntityVersionID = ev.idgk_EntityVersionID
left outer join RestrictedValuesVersion RVV on RVV.idgk_RestrictedValuesVersionID = etv.fk_RestrictedValuesVersionID
where s.idgk_SolutionID = sp.fk_SolutionID
and s. idgk_SolutionID = @Solution_ID
order by p.vch_ProjectName